﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;

namespace DBUtil.Util
{
    /// <summary>
    /// sql语句工具
    /// </summary>
    public class SqlUtil
    {
        /// <summary>
        /// 生成Insert语句和参数化数组
        /// </summary>
        /// <param name="db">数据库访问对象</param>
        /// <param name="tableName">表名</param>
        /// <param name="keyValues">键值对</param>
        /// <param name="paraPrefix">生成的参数化变量的前缀</param>
        /// <returns></returns>
        public static (string sql, IDataParameter[] paras) GenerateInsertSql(DBAccess db, string tableName, Dictionary<string, object> keyValues, string paraPrefix = null)
        {
            string insertTableOption = "";
            string insertTableValues = "";
            List<IDataParameter> paras = new List<IDataParameter>();
            foreach (var item in keyValues)
            {
                insertTableOption += " " + item.Key + ",";
                if (item.Value == null)
                {
                    insertTableValues += "null,";
                    continue;
                }
                object val = item.Value;
                var isPara = true;
                try
                {
                    var type = item.Value.GetType();
                    var args = type.GenericTypeArguments;
                    if (type.BaseType == typeof(ValueType) && type.GenericTypeArguments.Length == 2)
                    {
                        var fileds = ((TypeInfo)type).DeclaredFields.ToList();
                        if (fileds.Count == 2)
                        {
                            if (fileds[1].FieldType == typeof(bool))
                            {
                                val = fileds[0].GetValue(item.Value);
                                isPara = (bool)fileds[1].GetValue(item.Value);
                            }
                        }
                    }
                }
                catch { }
                if (isPara)
                {
                    var key = item.Key;
                    if (!string.IsNullOrWhiteSpace(paraPrefix))
                    {
                        key = paraPrefix + key;
                    }
                    insertTableValues += db.ParaPrefix + key + ",";
                    paras.Add(db.CreatePara(key, val));
                }
                else
                {
                    insertTableValues += val.ToString() + ",";
                }
            }
            insertTableOption = insertTableOption.TrimEnd(new char[] { ',' });
            insertTableValues = insertTableValues.TrimEnd(new char[] { ',' });

            string strSql = $"insert into {tableName} ({insertTableOption}) values ({insertTableValues});";
            return (strSql, paras.ToArray());
        }

        /// <summary>
        /// 生成Update语句和参数化数组
        /// </summary>
        /// <param name="db">数据库访问对象</param>
        /// <param name="tableName">表名</param>
        /// <param name="keyValues">键值对</param>
        /// <param name="filterSql">过滤sql以and开头</param>
        /// <param name="paraPrefix">生成的参数化变量的前缀</param>
        /// <param name="filterParas">过滤条件中的参数数组</param>
        /// <returns></returns>
        public static (string sql, IDataParameter[] paras) GenerateUpdateSql(DBAccess db, string tableName, Dictionary<string, object> keyValues, string paraPrefix, string filterSql, IDataParameter[] filterParas)
        {
            string sql = $"update {tableName} set ";
            List<IDataParameter> paras = new List<IDataParameter>();
            foreach (var item in keyValues)
            {
                if (item.Value == null)
                {
                    sql += " " + item.Key.ToString() + "=null,";
                }
                else
                {
                    object val = item.Value;
                    var isPara = true;
                    try
                    {
                        var type = item.Value.GetType();
                        var args = type.GenericTypeArguments;
                        if (type.BaseType == typeof(ValueType) && type.GenericTypeArguments.Length == 2)
                        {
                            var fileds = ((TypeInfo)type).DeclaredFields.ToList();
                            if (fileds.Count == 2)
                            {
                                if (fileds[1].FieldType == typeof(bool))
                                {
                                    val = fileds[0].GetValue(item.Value);
                                    isPara = (bool)fileds[1].GetValue(item.Value);
                                }
                            }
                        }
                    }
                    catch { }
                    if (isPara)
                    {
                        var key = item.Key;
                        if (!string.IsNullOrWhiteSpace(paraPrefix))
                        {
                            key = paraPrefix + key;
                        }
                        sql += " " + item.Key + $"={db.ParaPrefix}" + key + ",";
                        paras.Add(db.CreatePara(key, item.Value));
                    }
                    else
                    {
                        sql += " " + item.Key + "=" + val.ToString() + ",";
                    }
                }
            }
            sql = sql.TrimEnd(new char[] { ',' });
            sql += " where 1=1 ";
            sql += filterSql;
            if (filterParas != null) paras.AddRange(filterParas);
            return (sql, paras.ToArray());
        }

        /// <summary>
        /// 生成Where字句
        /// </summary>
        /// <param name="db">数据库访问对象</param>
        /// <param name="keyValues">键值对</param>
        /// <param name="paraPrefix">参数化前缀</param>
        /// <param name="filterParas">参数化数组</param>
        /// <returns></returns>
        public static (string filterSql, IDataParameter[] filterParas) GenerateFilterSql(DBAccess db, Dictionary<string, object> keyValues, string paraPrefix, IDataParameter[] filterParas)
        {
            string sql = "";
            List<IDataParameter> paras = new List<IDataParameter>();
            foreach (var item in keyValues)
            {
                if (item.Value == null)
                {
                    sql += " " + item.Key.ToString() + "=null,";
                }
                else
                {
                    object val = item.Value;
                    var isPara = true;
                    try
                    {
                        var type = item.Value.GetType();
                        var args = type.GenericTypeArguments;
                        if (type.BaseType == typeof(ValueType) && type.GenericTypeArguments.Length == 2)
                        {
                            var fileds = ((TypeInfo)type).DeclaredFields.ToList();
                            if (fileds.Count == 2)
                            {
                                if (fileds[1].FieldType == typeof(bool))
                                {
                                    val = fileds[0].GetValue(item.Value);
                                    isPara = (bool)fileds[1].GetValue(item.Value);
                                }
                            }
                        }
                    }
                    catch { }
                    if (isPara)
                    {
                        var key = item.Key;
                        if (!string.IsNullOrWhiteSpace(paraPrefix))
                        {
                            key = paraPrefix + key;
                        }
                        sql += " and " + item.Key + $"={db.ParaPrefix}" + key;
                        paras.Add(db.CreatePara(key, item.Value));
                    }
                    else
                    {
                        sql += " and " + item.Key + val.ToString();
                    }
                }
            }
            if (filterParas != null) paras.AddRange(filterParas);
            return (sql, paras.ToArray());
        }

        /// <summary>
        /// 生成Update语句和参数化数组
        /// </summary>
        /// <param name="db">数据库访问对象</param>
        /// <param name="tableName">表名</param>
        /// <param name="filterSql">过滤sql以and开头</param>
        /// <returns></returns>
        public static string GenerateDeleteSql(DBAccess db, string tableName, string filterSql)
        {
            string sql = $"delete from {tableName} ";
            return sql += $" where 1=1 {filterSql}";
        }
    }
}
